SQLite in Production: Not Just for Mobile

javi
Tarjeta de memoria SSD en primer plano sobre superficie oscura representando almacenamiento persistente

The myth: SQLite is for mobile apps and prototypes. Reality in 2024: thousands of production applications — from Tailscale to Fly.io, Expensify, or small SaaS — use it as main DB. The SQLite + WAL mode + Litestream + (optionally) LiteFS combination enables scaling beyond where many teams assume they need PostgreSQL. This article covers how, when, and its real limits.

Why Server-Side SQLite Is Viable

Traditional SQLite assumptions are obsolete:

  • “Doesn’t scale”: false. SQLite handles thousands of writes/s and hundreds of thousands of reads/s on modest hardware.
  • “No concurrent writes”: historically true, but WAL mode mitigates much.
  • “No replication”: Litestream, LiteFS, rqlite solve.
  • “No backup tool”: Litestream streams to S3-compatible.

For apps with 1 process / 1 instance, SQLite is spectacularly productive.

WAL Mode: The Fundamental Change

Write-Ahead Logging, enable with PRAGMA journal_mode=WAL:

  • Concurrent reads don’t block writes.
  • Concurrent writes still serialise but with less contention.
  • Better durability vs traditional journal_mode.
  • Incremental checkpointing from WAL to DB.

Almost every serious SQLite deployment uses WAL. It’s the reasonable default.

Productive Optimisations

Recommended pragmas for production:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;  -- vs FULL: durability/perf trade-off
PRAGMA cache_size = -64000;   -- 64MB
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;   -- 5s before SQLITE_BUSY
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456; -- 256MB memory-mapped

3-10x performance differences vs defaults.

Litestream: Replication to S3

Litestream streams the WAL to S3 (or compatible) in near-realtime:

# /etc/litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        bucket: my-backups
        path: app.db
        region: eu-west-1
        access-key-id: ${AWS_ACCESS_KEY_ID}
        secret-access-key: ${AWS_SECRET_ACCESS_KEY}

Trivial restore:

litestream restore -o /data/app.db s3://my-backups/app.db

For productive backup, Litestream makes SQLite nearly comparable to Postgres in durability.

LiteFS: Multi-Node Replication

LiteFS (from Fly.io) goes further: full SQLite replication between nodes with FUSE filesystem.

  • Primary + replicas: writes to primary, reads on any node.
  • Strong consistency with leasing.
  • Automated failover.

Useful for high-availability or global applications (Fly.io uses this for its own Postgres-clustering alternative).

rqlite: Distributed SQLite

rqlite is another approach: SQLite with Raft for distributed consensus. More complex but full cluster.

For cases of 3+ nodes with automatic failover and strict replication, rqlite is more robust than LiteFS.

Real Cases

  • Tailscale: coordination server in SQLite + Litestream.
  • Expensify: Bedrock (distributed wrapper) in SQLite, millions of users.
  • PocketBase: complete BaaS based on SQLite.
  • Linear and Notion: SQLite on client via WASM.

Common pattern: one app node + local SQLite + Litestream offsite.

When SQLite Beats Postgres

Cases where SQLite is better choice:

  • 1-process app (single instance VM, monolithic Lambda).
  • Simple to moderate queries, no cross-server joins.
  • Data volume up to ~100GB comfortably, ~1TB with discipline.
  • Zero latency to DB (same process).
  • Simple deployment (no separate DB server).
  • Backup as file cp.

When SQLite Isn’t Enough

Honesty:

  • Multiple writing processes: write serialisation is bottleneck.
  • Multiple app instances: can’t shard SQLite between servers without complexity.
  • Massive analytical queries: use DuckDB (SQLite is OLTP, DuckDB is OLAP).
  • Postgres-specific extensions (serious pgvector, PostGIS, etc).
  • DB-level roles/permissions: SQLite doesn’t have.

Write Concurrency

For write-heavy apps, SQLite has a global write lock. Strategies:

  • Batching writes: small grouped transactions.
  • BEGIN IMMEDIATE for upgrade to write lock before retry-pain.
  • App-level queue: single writer processing requests.
  • Segregation: write-heavy data in DB separate from read-heavy.

For ~1000 sustained writes/s, SQLite handles. >10k sustained, consider Postgres.

Migrations

Use sqlc (Go), SQLx (Rust), better-sqlite3 (Node). Tooling comparable to Postgres.

For schema migrations:

Specific SQL differences (types, ALTER TABLE limitations) require care.

Useful Extensions

Recent ecosystem growth is strong.

Encryption

For at-rest sensitive data, these tools are mature.

Conclusion

SQLite is a serious option for 1-instance production applications. With WAL mode, optimised pragmas, Litestream, and optionally LiteFS or rqlite for HA, it covers cases many teams assume require PostgreSQL. The operational advantage is huge: no DB server, no network, no permissions, no complex backup. For massive write-heavy loads or multi-instance-writing, Postgres remains correct. But for the huge majority of small-to-medium apps, starting with SQLite and growing to Postgres only if really needed is a pragmatic strategy.

Follow us on jacar.es for more on SQLite, databases, and simple architectures.

Entradas relacionadas